package com.sinodata.bsm.center.dao.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.sinodata.bsm.center.bean.Condition;
import com.sinodata.bsm.center.bean.Page;
import com.sinodata.bsm.center.dao.BaseDao;
import com.sinodata.bsm.center.dao.ResDao;
import com.sinodata.bsm.common.vo.Res;

@Repository
public class ResDaoImpl extends BaseDao implements ResDao {

    /*
     * (non-Javadoc)
     * 
     * @see com.sinodata.bsm.center.dao.ResDao#findByPage(com.sinodata.bsm.center.bean.Page)
     */
    @SuppressWarnings("unchecked")
    @Override
    public List<Res> findByPage(Page<Res> page, Condition condition) {
        StringBuilder hql = new StringBuilder("from Res where 1=1 and deleted <>1 ");
        Map<String, String> parameter = condition.getWhere();
        Iterator<String> keys = parameter.keySet().iterator();
        while (keys.hasNext()) {
            String key = keys.next();
            String value = parameter.get(key).toLowerCase();
            int parameterIndex = 0;
            String parameterType = key.substring(key.lastIndexOf("_") + 1, key.lastIndexOf("_") + 2);

            // 约束条件
            if (key.indexOf("EQ") != -1) {
                parameterIndex = 1;
            } else if (key.indexOf("LIKE") != -1) {
                parameterIndex = 2;
            } else if (key.indexOf("LT") != -1) { // 小于
                parameterIndex = 3;
            } else if (key.indexOf("GT") != -1) { // 大于
                parameterIndex = 4;
            } else if (key.indexOf("GE") != -1) { // 大于等于
                parameterIndex = 5;
            } else if (key.indexOf("LE") != -1) { // 小于等于
                parameterIndex = 6;
            } else if (key.indexOf("OR") != -1) { // 或者
                parameterIndex = 7;
            } else if (key.indexOf("NOT") != -1) {
                parameterIndex = 8;
            }

            if (parameterType.equals("S")) {
                value = "'" + value + "'";
            }

            key = key.substring(key.lastIndexOf("_") + 2);

            // 拼接where 条件
            switch (parameterIndex) {
                case 1:
                    hql.append(" and " + key + " = " + value + " ");
                    break;
                case 2:
                    value = "'%" + value.toString().replace("'", "") + "%'";
                    hql.append(" and lower(" + key + ") like " + value + "");
                    break;
                case 3:
                    hql.append(" and " + key + " < " + value + "");
                    break;
                case 4:
                    hql.append(" and " + key + " > " + value + "");
                    break;
                case 5:
                    hql.append(" and " + key + " >= " + value + "");
                    break;
                case 6:
                    hql.append(" and " + key + " <= " + value + "");
                    break;
                case 7:
                    hql.append(" or " + key + " = " + value + "");
                    break;
                case 8:
                    hql.append(" and " + key + " <> " + value + "");
                    break;
            }
        }

        // 拼接排序
        Map<String, String> orderMap = page.getOrder();
        Iterator<String> orderKeys = orderMap.keySet().iterator();
        while (orderKeys.hasNext()) {
            String orderCol = orderKeys.next();
            String orderValue = orderMap.get(orderCol);
            hql.append("order by ").append(orderCol).append(" " + orderValue + " ");
        }
        page.setTotal(this.getTotal(hql.toString()));
        List<Res> res = getSession().createQuery(hql.toString()).setFirstResult(page.getFirstResultNo()).setMaxResults(page.getPageSize()).list();
        page.setRows(res);
        return res;
    }

    public int getTotal(String hql) {
        StringBuilder totalHql = new StringBuilder("select count(*)  ");
        totalHql.append(hql.toString());
        int total = Integer.parseInt(getSession().createQuery(totalHql.toString()).uniqueResult().toString());
        return total;
    }

    @Override
    public Set<Long> findPausedResIds() {
        Set<Long> set = new HashSet<Long>();
        List<BigDecimal> list = getSession().createSQLQuery(" select t.id as id from res t where (t.deleted=0 or  t.deleted is null) and t.paused=1").list();
        if (list != null)
            for (BigDecimal bd : list) {
                set.add(bd.longValue());
            }
        return set;
    }

    @SuppressWarnings("unchecked")
    public Page search(Page page, Condition condition, String resTypeId, List<String> dimensionArray) {
        StringBuilder sql = new StringBuilder("select id,res_type_id,ip,name,desc_,paused,probe_id,health_status from res where 1=1 and deleted <> 1");
        sql.append(getResTypeSql(resTypeId));
        sql.append(getDimensionSql(dimensionArray));
        Map<String, String> parameter = condition.getWhere();
        Iterator<String> keys = parameter.keySet().iterator();
        while (keys.hasNext()) {
            String parameterName = keys.next(); //参数原始值 LIKE_S**
            String parameterValue = parameter.get(parameterName); //FILTER_EQ_Iprobe_Id //参数值
            if ("resIds".equals(parameterName)) { //资源授权
                sql.append(" and id in ( select id from res where id in(" + parameterValue + ") union ");
                sql.append(" select end_id from res_relation r   start with start_id in (" + parameterValue + ") connect by prior end_id=start_id ) ");
                continue;
            }
            String parameterType = parameterName.substring(parameterName.lastIndexOf("_") + 1, parameterName.lastIndexOf("_") + 2); //参数类型 S、I ...
            String conditionSymbol = parameterName.substring(0, parameterName.indexOf("_")); //条件符 LIKE、EQ ...
            String newParameterName = parameterName.substring(parameterName.indexOf("_") + 2); //参数

            int tempIndex = 0; //条件符号临时索引
            // 约束条件
            if (conditionSymbol.indexOf("EQ") != -1) {
                tempIndex = 1;
            } else if (conditionSymbol.indexOf("LIKE") != -1) {
                tempIndex = 2;
            } else if (conditionSymbol.indexOf("LT") != -1) { // 小于
                tempIndex = 3;
            } else if (conditionSymbol.indexOf("GT") != -1) { // 大于
                tempIndex = 4;
            } else if (conditionSymbol.indexOf("GE") != -1) { // 大于等于
                tempIndex = 5;
            } else if (conditionSymbol.indexOf("LE") != -1) { // 小于等于
                tempIndex = 6;
            } else if (conditionSymbol.indexOf("OR") != -1) { // 或者
                tempIndex = 7;
            } else if (conditionSymbol.indexOf("NOT") != -1) {
                tempIndex = 8;
            } else if (conditionSymbol.indexOf("IN") != -1) {
                tempIndex = 9;
            }

            if (parameterType.equals("S")) {
                parameterValue = "'" + parameterValue + "'";
            }

            switch (tempIndex) {
                case 1:
                    sql.append(" and " + newParameterName + " = " + parameterValue + " ");
                    break;
                case 2:
                    parameterValue = "'%" + parameterValue.toString().replace("'", "") + "%'";
                    sql.append(" and " + newParameterName + " like " + parameterValue + "");
                    break;
                case 3:
                    sql.append(" and " + newParameterName + " < " + parameterValue + "");
                    break;
                case 4:
                    sql.append(" and " + newParameterName + " > " + parameterValue + "");
                    break;
                case 5:
                    sql.append(" and " + newParameterName + " >= " + parameterValue + "");
                    break;
                case 6:
                    sql.append(" and " + newParameterName + " <= " + parameterValue + "");
                    break;
                case 7:
                    sql.append(" or " + newParameterName + " = " + parameterValue + "");
                    break;
                case 8:
                    sql.append(" and " + newParameterName + " <> " + parameterValue + "");
                    break;
                case 9:
                    sql.append(" and  " + newParameterName + " in (" + parameterValue + " )");
            }

        }
        page.setTotal(this.getTotalBySql(sql.toString(), resTypeId, dimensionArray));
        List<Object> list = this.getSession().createSQLQuery(sql.toString()).setFirstResult(page.getFirstResultNo()).setMaxResults(page.getPageSize()).list();
        page.setRows(list);
        return page;
    }

    /**
     * 获取查询结果总数(sql)
     * @param sql
     * @return
     */
    public int getTotalBySql(String sql, String resTypeId, List<String> dimensionArray) {
        StringBuilder totalSql = new StringBuilder("select count(*) from (");
        totalSql.append(sql);
        totalSql.append(getResTypeSql(resTypeId));
        totalSql.append(getDimensionSql(dimensionArray));
        totalSql.append(")");
        int total = Integer.parseInt(getSession().createSQLQuery(totalSql.toString()).uniqueResult().toString());
        return total;
    }

    private StringBuilder getResTypeSql(String resTypeId) {
        StringBuilder sb = new StringBuilder();
        if (resTypeId != null && !"".equals(resTypeId)) {
            sb.append(" and res.res_type_id in (");
            sb.append(" select id from res_type start with id='");
            sb.append(resTypeId);
            sb.append("' connect by prior id=parent_id");
            sb.append(" )");
        }
        return sb;
    }

    private StringBuilder getDimensionSql(List<String> dimensionArray) {
        StringBuilder sb = new StringBuilder();
        if (dimensionArray.size() != 0) {
            sb.append(" and res.id in (");
            sb.append(" select distinct res_id from res_attr_val where (");
            for (String dimension : dimensionArray) {
                sb.append("attr_id in(select id from attribute where data_type_id=5 and data_unit=1) and attr_value in(");
                sb.append("select to_char(car.id) from ci_attr_range car start with id='");
                sb.append(dimension);
                sb.append("' connect by prior id=parent_id");
                sb.append(") and ");
            }
            sb.delete(sb.lastIndexOf(" and"), sb.length());
            sb.append("))");
        }
        return sb;
    }

    @SuppressWarnings("unchecked")
    public List<Object> findParentIdsByResId(Long resId) {
        String sql = "select start_id from res_relation r   start with end_id =" + resId + " connect by prior   start_id= end_id ";
        List<Object> parentIds = this.getSession().createSQLQuery(sql).list();
        return parentIds;
    }

    @Override
    public List<BigDecimal> findResIdByMonitorType(Long monitorTypeId) {
        StringBuffer bf = new StringBuffer();
        bf.append("select id from res where deleted = 0 and res_type_id in ( ");
        bf.append(" select id  from res_type start with id in ( select res_type_id ");
        bf.append(" from moti_restype_rel t where moit_type_id = " + monitorTypeId + " and self_flag = 1)");
        bf.append(" connect by prior id = parent_id ) order by id ");
        System.out.println(bf.toString());
        return getSession().createSQLQuery(bf.toString()).list();
    }

    @Override
    public List<BigDecimal> findNoRelationResId() {
        StringBuffer bf = new StringBuffer();
        bf.append(" select id from res where deleted = 0 ");
        bf.append(" and  id  not in( select end_id from res_relation ) ");
        bf.append(" and res_type_id not in ( ");
        bf.append(" select id  from res_type start with id in ( select res_type_id ");
        bf.append(" from moti_restype_rel t where moit_type_id < 80000 and self_flag = 1)");
        bf.append(" connect by prior id = parent_id ) and res_type_id != 1000000000000 order by id ");
        return getSession().createSQLQuery(bf.toString()).list();
    }

    @Override
    public List<Res> findByResTypeId(Long resTypeId) {
        StringBuilder sb = new StringBuilder("select t.Id,t.Res_Type_Id,t.Probe_Id,t.Ip,t.Internal_Id,t.Name,t.Desc_,t.Paused,t.Health_Status,t.Code,t.Deleted,t.Health_Degree from res t where res_type_id in ( select id from res_type  start with id = ");
        sb.append(resTypeId);
        sb.append(" connect by prior id = parent_id ) and deleted = 0");
        Query query = getSession().createSQLQuery(sb.toString());
        @SuppressWarnings("unchecked")
        List<Object[]> list = query.list();
        List<Res> result = new ArrayList<Res>();
        for (Object[] o : list) {
            Res res = new Res();
            res.setId(Long.valueOf(String.valueOf(o[0])));
            if (o[1] != null) {
                res.setResTypeId(Long.valueOf(String.valueOf(o[1])));
            }
            if (o[2] != null) {
                res.setProbeId(Long.valueOf(String.valueOf(o[2])));
            }
            if (o[3] != null) {
                res.setIp(String.valueOf(o[3]));
            }
            if (o[4] != null) {
                res.setInternalId(String.valueOf(o[4]));
            }
            if (o[5] != null) {
                res.setName(String.valueOf(o[5]));
            }
            if (o[6] != null) {
                res.setDesc(String.valueOf(o[6]));
            }
            if (o[7] != null) {
                res.setPaused(Integer.valueOf(String.valueOf(o[7])));
            }
            if (o[8] != null) {
                res.setHealthStatus(Integer.valueOf(String.valueOf(o[8])));
            }
            if (o[9] != null) {
                res.setCode(String.valueOf(o[9]));
            }
            if (o[10] != null) {
                res.setDeleted(Integer.valueOf(String.valueOf(o[10])));
            }
            if (o[11] != null) {
                res.setHealthDegree(Integer.valueOf(String.valueOf(o[11])));
            }
            result.add(res);
        }
        return result;
    }

    @Override
    public Long getMyPropertyIdByDateTypeIdResTypeId(Integer dateTypeId, Long resTypeId, Long resId) {
        StringBuilder sb = new StringBuilder("select min(id) as id from property t where t.data_type_id = ");
        sb.append(dateTypeId);
        sb.append(" and t.res_type = ");
        sb.append(resTypeId);
        sb.append(" and id not in ( select prop_id from property_display where res_id = ");
        sb.append(resId);
        sb.append(" ) order by id ");
        return Long.parseLong(getSession().createSQLQuery(sb.toString()).uniqueResult().toString());
    }
}
